﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using ERPStudio;
using System.Data.SqlClient;
using System.IO;
using System.Globalization;
using System.Data.OleDb;
using System.Diagnostics;
using System.Text.RegularExpressions;

namespace AMix
{
    public partial class ImportFrm : FrmRezumat
    {
        public class FileNodeInfo
        {
            public String NumeUnitate = String.Empty;
            public int An = DateTime.Now.Year;
            public int Luna = DateTime.Now.Month;
            public Boolean seImporta = false;
            public String[] tipuri = new String[20]; 
            public FileNodeInfo(String id)
            {
                NumeUnitate = id;
            }
            public FileNodeInfo(String id,int vAn,int vLuna)
            {
                NumeUnitate = id;
                An = vAn;
                Luna = vLuna;
            }


        }

        ToolStripProgressBar pBar = new ToolStripProgressBar();
        private String TipUnitate = String.Empty;
        private int LunaCurenta = DateTime.Now.Month;
        private int AnulCurent = DateTime.Now.Year;

        public ImportFrm(ModuleClass mInfo)
            : base(mInfo)
        {
            InitializeComponent();
            PerioadaCurentaDTP.Format = DateTimePickerFormat.Custom;
            PerioadaCurentaDTP.CustomFormat = "MM.yyyy";
        }

        private void PopuleazaConturi(String TipUnitate, SqlConnection con)
        {
            #region Adaugare cont N/A
            ///verifica existenta contului radacina pentru unitatea curenta
            ///
            SqlCommand cmd = new SqlCommand("SELECT Count(*) FROM [Conturi] WHERE [CodUnitate]=" + ModuleInfo.CodUnitate + " AND [Cont]='N/A'",
                    con);
            cmd.CommandTimeout = 180;
            Object ret = cmd.ExecuteScalar();
            if (ret != null && Convert.ToInt32(ret) == 0)
            {
                cmd.CommandText = "INSERT INTO [Conturi]([Cont],[CodUnitate],[Denumire],[Tip],[Terminal],[ContParinte],[EsteAnalitic],[Categorie]) VALUES ('N/A'," + ModuleInfo.CodUnitate.ToString() + ",'Principal','B',0,'N/A',1,0)";
                cmd.ExecuteNonQuery();
            }
            #endregion

            cmd.CommandText = "SELECT Count(*) FROM [Conturi] WHERE [CodUnitate]=" + ModuleInfo.CodUnitate + " AND [Cont]<>'N/A'";
            ret = cmd.ExecuteScalar();
            if (ret != null && Convert.ToInt32(ret) != 0)
                return;

            cmd.CommandText = "INSERT INTO [Conturi]([Cont],[Denumire],[Tip],[Terminal],[ContParinte],[EsteAnalitic],[Categorie],[CodUnitate],[DebitInitial],[CreditInitial],[DebitPrecedent],[CreditPrecedent],[RulajDebit] ,[RulajCredit])" +
                "SELECT  [Cont],[Denumire],[Tip],[Terminal],[ContParinte],0,[Categorie]," + ModuleInfo.CodUnitate.ToString() + ",0,0,0,0,0,0 FROM [PlanConturi] pl WHERE [TipUnitate]='" + TipUnitate + "' AND  [Preluare] = 1 " +
                " AND [ContParinte] in (SELECT [CONT] FROM [Conturi] where [CodUnitate]=" + ModuleInfo.CodUnitate.ToString() +
                ") AND pl.[Cont] NOT IN (SELECT [CONT] FROM [Conturi] where [CodUnitate]=" + ModuleInfo.CodUnitate.ToString() +
                ")";
            int caterec = 1;
            while (caterec != 0)
            {
                try
                {
                    caterec = cmd.ExecuteNonQuery();
                }
                catch (SqlException ex)
                {
                    MessageBox.Show(ex.ToString());
                }
            }
        }

        /// <summary>
        /// data setul pentru import care v-a fi incarcat dinamic
        /// </summary>
        private DataSet ImportDataSet = new DataSet("ImportDataSet");

        private void ImportFrm_Load(object sender, EventArgs e)
        {
            
            
            //tabControl1.TabPages.Remove(tabPage2);
            SqlConnection con = DeschideConexiune(workConnString);
            this.tipDepartamentTableAdapter.Connection = con;
            this.tipDepartamentTableAdapter.Fill(this.eSRPDataSet.TipDepartament);

            this.departamenteTableAdapter.Connection = con;
            this.departamenteTableAdapter.Fill(this.eSRPDataSet.Departamente, ModuleInfo.CodUnitate);
            Object ret = GetSetupValue("BalconSetup", "TipUnitate", ModuleInfo.CodUnitate, con);
            String val = String.Empty;
            if (ret == null || ret == DBNull.Value)
            {
                while (ret == null)
                {
                    MessageBox.Show("Modulul Balante Contabile nu a fost configurat. \nVa rugam sa-l configurati!",
                            "Eroare",
                            MessageBoxButtons.OK,
                            MessageBoxIcon.Hand);
                    #region Configurare Modul BalCon
                    Type mModule = null;
                    FrmCard newFrm = RuleazaFrm("erpforms", "ERPForms.BalconSetupFrm", new object[] { ModuleInfo }, out mModule) as FrmCard;
                    if (newFrm == null)
                    {
                        MyTrace("PartenerFrm:: GetContPartener(SqlConnection conn,String tip) - eroare rulare");
                        MessageBox.Show("Eroare Program, va rugam sa va adresati distribuitorului!");
                        return;
                    }
                    newFrm.ShowDialog();
                    #endregion
                    ///configurare modul-----------------------------------
                    ret = GetSetupValue("BalconSetup", "TipUnitate", ModuleInfo.CodUnitate, con);
                }
            }
            TipUnitate = ret.ToString();
            PopuleazaConturi(TipUnitate,con);
            InchideConexiune(con);
            
            if (!EsteArhiva())
            {
                AddButton("Obiecte Inventar excel", global::ERPStudio.Properties.Resources._001_09, ImportExcel, ToolStripItemAlignment.Right, true);
                AddButton("Import[F9]", global::ERPStudio.Properties.Resources._001_07, Import, ToolStripItemAlignment.Right, false);
                AddButton("In calea...[F11]", global::ERPStudio.Properties.Resources.salvare1, InCalea, ToolStripItemAlignment.Right, true);

                HandleSalvare += Import;
                HandleF11 += InCalea;
            }
        }

        private void ImportExcel(object sender, EventArgs e)
        {
            Amix.FrmExcel frm = new Amix.FrmExcel(String.Empty, ModuleInfo) ;
            frm.ShowDialog();
        }

        
        private void InCalea(object sender, EventArgs e)
        { 
            openFileDialog1.AddExtension = true;
            openFileDialog1.FilterIndex = 1;
            openFileDialog1.RestoreDirectory = true;
            openFileDialog1.FileName = "*.dbf";
            openFileDialog1.DefaultExt = ".dbf";
            openFileDialog1.Filter = "FoxPro files (*.dbf)|*.dbf";
            if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                FileInfo fi = new FileInfo(openFileDialog1.FileName);
                ProceseazaDirector(fi.DirectoryName);
            }
            SetStatus("Scanat director...OK");
        }

        private Boolean InCalea()
        {
            openFileDialog1.AddExtension = true;
            openFileDialog1.FilterIndex = 1;
            openFileDialog1.RestoreDirectory = true;
            openFileDialog1.FileName = "*.dbf";
            openFileDialog1.DefaultExt = ".dbf";
            openFileDialog1.Filter = "FoxPro files (*.dbf)|*.dbf";
            if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                FileInfo fi = new FileInfo(openFileDialog1.FileName);
                ProceseazaDirector(fi.DirectoryName);
            }
            else
                return false;
            SetStatus("Scanat director...OK");
            return true;
        }

        private void ProceseazaDirector(String cale)
        { 
             ///Cauta daca exista directorul date
             ///e_date = daca exista directorul date
             ///e_cfg = daca exista directorul de configurare
	        String cl = cale+"\\Date",unit=String.Empty,nume_fcfg=String.Empty;
            if(!Directory.Exists(cl))
            {
                MessageBox.Show("Lipseste directorul Date = " + cl);
                return;
            }
            else
            {
		        nume_fcfg = cale+"\\amix.dbf";
                if(ImportTC.SelectedTab == MagnumTP)
                    nume_fcfg = cale + "\\magnum.dbf";
		        if(!File.Exists(nume_fcfg))
                {
			       MessageBox.Show("Lipseste fisierul amix.dbf");
                   return; 
                }
            }
		
            cale = cale.Trim();

            TreeView fisTV = FisiereTV;
            if (ImportTC.SelectedTab == MagnumTP)
                fisTV = FisiereMagnumTV;
            fisTV.Nodes.Clear();
            TreeNode tnRoot = fisTV.Nodes.Add(cale);
            //tnRoot.Tag = new NodeInfo(cod);
            String strItem = String.Empty;
            String ConnectionString = "Provider=VFPOLEDB.1;" + "Data Source = " + cale  +";";
            System.Data.OleDb.OleDbConnection dBaseConnection = null;
            dBaseConnection = new System.Data.OleDb.OleDbConnection(ConnectionString);
            System.Data.OleDb.OleDbDataReader dbCfg = null;
            System.Data.OleDb.OleDbDataReader dbRul = null;
            int m_nIndex = 0,vanarh=0;
            try
            {
                dBaseConnection.Open();
                System.Data.OleDb.OleDbCommand dBaseCommand= new System.Data.OleDb.OleDbCommand("SELECT * FROM [Amix]", dBaseConnection);
                if (ImportTC.SelectedTab == MagnumTP)
                    dBaseCommand.CommandText = "SELECT * FROM [Magnum]";

                dbCfg = dBaseCommand.ExecuteReader();
                if(dbCfg==null){
                    return;
                }
                while (dbCfg.Read())
                {
                    Int16 anc = (Int16)AnulCurent;
                    if (ImportTC.SelectedTab == AmixTP)
                        anc = Convert.ToInt16(dbCfg["AN_CRT"]);
                    DateTime curenta = new DateTime(anc, Convert.ToInt16(dbCfg["LUNA_CLO"]), 1);

                    curenta = curenta.AddMonths(1);
                    SalveazaPerioadaCurenta(curenta);
                    strItem = dbCfg["PATH_DATE"].ToString();
                    if (ImportTC.SelectedTab == MagnumTP)
                        strItem = dbCfg["PATH_MAG"].ToString();
                    strItem = strItem.Trim();
                    strItem = strItem.ToUpper();
                    if (ImportTC.SelectedTab == AmixTP)
                        strItem = strItem.Replace("\\AMIX",cale);
                    if (ImportTC.SelectedTab == MagnumTP)
                        strItem = strItem.Replace("\\MAGNUM", cale);

                    if (!strItem.Contains(":"))
                        strItem = cale + "\\" + strItem;
                    FileNodeInfo info = new FileNodeInfo(strItem);
                    TreeNode addNode = null;
                    String numeUnit = dbCfg["TITL_1"].ToString().Trim();
                    if (ImportTC.SelectedTab == AmixTP)
                        addNode = tnRoot.Nodes.Add(numeUnit == String.Empty ? "Fara nume" : numeUnit);
                    if (ImportTC.SelectedTab == MagnumTP)
                        addNode = tnRoot.Nodes.Add(dbCfg["NUME_GEST"].ToString().Trim());
                    info.seImporta = true;
                    if (ImportTC.SelectedTab == MagnumTP)
                    {
                        for (int i = 1; i <= 15; i++)
                        {
                            info.tipuri[i - 1] = dbCfg["Tip" + i.ToString()].ToString().Trim();
                        }
                    }
                    addNode.Tag = info;
                    SearchAnArhive(addNode, strItem, addNode.Text, m_nIndex, ref vanarh);
                }
            }
            catch(Exception ex)
            {
                MessageBox.Show(ex.ToString());
                return;
            }
            finally
            {
                if(dbCfg!=null)
                    dbCfg.Close();
                if (dbRul != null)
                    dbRul.Close();
                dBaseConnection.Close();
            }
            tnRoot.ExpandAll();

        }

        private void SalveazaPerioadaCurenta(DateTime curenta)
        {
            if (curenta.Year * 100 + curenta.Month <= AnulCurent * 100 + LunaCurenta)
                return;
            SqlConnection con = DeschideConexiune(workConnString);
            SqlCommand com = new SqlCommand("UPDATE [AmixSetup] SET [LunaCurenta]=" + curenta.Month.ToString() + ",[AnulCurent]=" + curenta.Year.ToString() +
                " WHERE [CodUnitate]=" + ModuleInfo.CodUnitate.ToString(), con);
            com.CommandTimeout = 180;
            try
            {
                com.ExecuteNonQuery();
                LunaCurenta = curenta.Month;
                AnulCurent = curenta.Year;

            }
            catch (Exception e)
            {
                MessageBox.Show(e.ToString());
            }

            InchideConexiune(con);
        }

        private void SearchAnArhive(TreeNode nod, String cale, String NumeUnit,int UParinte, ref int vanarh)
        {
            vanarh = 0;
            try
            {
                DirectoryInfo di = new DirectoryInfo(cale);
                DirectoryInfo[] dirs = di.GetDirectories("Arh*.*",SearchOption.TopDirectoryOnly);
                int an = 0,maxan=0;
                foreach (DirectoryInfo diNext in dirs)
                {
                    String numef = diNext.Name;
                    String nume_cale_noua = cale + diNext.Name;
                    numef = numef.ToLower();
                    numef = numef.Replace("arh", String.Empty);
                    if (numef == "iva")
                    {
                        an = 0;
                        nume_cale_noua = "PERIOADA CURENTA";
                    }
                    else
                        an = Convert.ToInt32(numef);
                    maxan = System.Math.Max(maxan, an);
                    if (an == 0)
                        an = maxan + 1;
                    vanarh = maxan + 1;
                    TreeNode na = nod.Nodes.Add(nume_cale_noua);
                    FileNodeInfo infoCont = new FileNodeInfo(cale + diNext.Name);
                    infoCont.An = an;
                    infoCont.seImporta = false;
                    na.Tag = infoCont;
                    //SearchLuniArhive(na, nume_cale_noua + "\\", an, NumeUnit, UParinte);
                }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.ToString());
            }

        }

        private void ImportArhive(String cale,int an, SqlConnection con)
        {
            DirectoryInfo di = new DirectoryInfo(cale);
            String lastCommand = String.Empty;

            FileInfo[] fi = di.GetFiles("mix*.arh");
            int luna = 0;
            String ConnectionString = String.Empty;
            pBar.Value = 0;
            String Cont_Parinte = String.Empty, den = String.Empty, grupa = String.Empty, subgrupa = String.Empty, clasa = String.Empty, subclasa = String.Empty, familia = String.Empty,
                sint = String.Empty, an1 = String.Empty, an2 = String.Empty;
            Int32 Nrinv = 0;
            Int32 idclas = 0, durn = 0;
            Int16 ani = 0, lunai = 0, ano = 0, lunao = 0, sec = 0, regim = 0;
            System.Data.OleDb.OleDbConnection dBaseConnection = null;
            System.Data.OleDb.OleDbDataReader dbCont = null;

            SqlCommand cmd = new SqlCommand("", con);
            cmd.CommandTimeout = 180;
            //cmd.Transaction = tran;

            foreach (FileInfo fiTemp in fi)
            {
                String numef = fiTemp.Name;
                String numefr = fiTemp.Name;
                numefr = numefr.ToLower();
                numefr = numefr.Replace(".arh", string.Empty);
                numef = numef.ToLower();
                String nume_fis_doc = numef;
                nume_fis_doc = nume_fis_doc.ToUpper();
                nume_fis_doc = nume_fis_doc.Replace("MIX", "MIS");
                String nume_cale_noua = cale + fiTemp.Name;
                numef = numef.Replace("mix", String.Empty);
                numef = numef.Replace(".arh", String.Empty);
                luna = Convert.ToInt32(numef);
                nume_cale_noua = nume_cale_noua.Trim();
                //deschidem fisierul cu rulaje
                ConnectionString = "Provider=VFPOLEDB.1;" + "Data Source = " + cale + ";";
                NumeFileTB.Text = cale+fiTemp.Name;
                Application.DoEvents();
                dBaseConnection = new System.Data.OleDb.OleDbConnection(ConnectionString);
                Decimal valinv = Decimal.Zero, vala = Decimal.Zero;
                try
                {
                    dBaseConnection.Open();
                    System.Data.OleDb.OleDbCommand dBaseCommand = new System.Data.OleDb.OleDbCommand("SELECT * FROM [" + fiTemp.Name + "]", dBaseConnection);
                    dbCont = dBaseCommand.ExecuteReader();
                    //tran = con.BeginTransaction();
                    if (dbCont != null)
                    {
                        //daca are o inregistrare
                        pBar.Value = 0;
                        pBar.Maximum = dbCont.RecordsAffected;
                        while (dbCont.Read())
                        {
                            
                            try
                            {

                                valinv = Decimal.Zero; 
                                vala = Decimal.Zero;
                                if (dbCont["nrinv"].ToString() == String.Empty)
                                    continue;
                                
                                Nrinv = Convert.ToInt32(dbCont["nrinv"]);
                                MyTrace("Import:" + Nrinv.ToString());
                                den = dbCont["den"].ToString().Trim();
                                grupa = dbCont["grupa"].ToString().Trim();
                                subgrupa = dbCont["subgrupa"].ToString().Trim();
                                clasa = dbCont["clasa"].ToString().Trim();
                                subclasa = dbCont["subclasa"].ToString().Trim();
                                familia = dbCont["familia"].ToString().Trim();

                                ani = Convert.ToInt16(dbCont["ani"]);
                                if(ani==0) ani = Convert.ToInt16(an);

                                ano = Convert.ToInt16(dbCont["ano"]);                                    
                                if (ano == 0) ano = Convert.ToInt16(an);

                                lunai = Convert.ToInt16(dbCont["lni"]);
                                if (lunai == 0) lunai = Convert.ToInt16(luna); //o mica corectie

                                DateTime dai = new DateTime(ani, lunai, 1);
                                DateTime dpf = dai.AddMonths(-1);
                                lunao = Convert.ToInt16(dbCont["lno"]);
                                if (lunao == 0) lunao = Convert.ToInt16(luna); //o mica corectie

                                sec = Convert.ToInt16(dbCont["sec"]); //sectia
                                durn = Convert.ToInt32(dbCont["durn"]);
                                dai = dai.AddYears(durn);
                                if (!Decimal.TryParse(dbCont["valinv"].ToString(), out valinv))
                                {
                                    MyTrace("Eroare la valoarea de inventar la nr. inventar:" + Nrinv.ToString());
                                }

                                //valinv = Convert.ToDecimal(dbCont["valinv"].ToString());
                                vala = Convert.ToDecimal(dbCont["vala"]);
                                Decimal ra = Convert.ToDecimal(dbCont["ra"]);
                                Decimal amoan = Convert.ToDecimal(dbCont["amoan"]);
                                sint = dbCont["conts"].ToString();
                                an1 = dbCont["contan1"].ToString().Trim();
                                an2 = dbCont["contan2"].ToString().Trim();
                                if (Convert.ToInt16(an1) == 0)
                                    an1 = String.Empty;
                                if (Convert.ToInt16(an2) == 0)
                                    an2 = String.Empty;


                                String numeC = sint + (an1 != String.Empty ? "." + an1 : String.Empty) + (an2 != String.Empty ? "." + an2 : String.Empty);
                                regim = Convert.ToInt16(dbCont["regim"]); //sectia

                                MyTrace("Import:" + Nrinv.ToString() +"=OK");
                                cmd.CommandText = "SELECT * FROM [AmixRegistru] WHERE [IDMijlocFix]=" + Nrinv.ToString();
                                SqlDataReader dr = cmd.ExecuteReader();
                                if (dr != null)
                                {
                                    if (dr.HasRows)
                                    {
                                        if (dr.Read())
                                        {
                                            dr.Close();

                                            cmd.CommandText = "INSERT INTO [AmixAmortizare] ([IDMijlocFix],[Luna],[An],[ValoareInventar],[ValoareAmortizata],[ValoareRamasa],[AmortizareAn],[AmortizareLuna]" +
                                            ",[DurataNormata],[LuniAmortizate],[Blocat]) VALUES (" +
                                            Nrinv.ToString() + "," + luna.ToString() + "," + an.ToString() + "," + Convert.ToDouble(dbCont["valinv"]).ToString("F2", CultureInfo.InvariantCulture) + "," +
                                            vala.ToString("G", (CultureInfo.InvariantCulture)) + "," + (valinv - vala).ToString("G", (CultureInfo.InvariantCulture)) + "," +
                                            amoan.ToString("G", (CultureInfo.InvariantCulture)) + "," + ra.ToString("G", (CultureInfo.InvariantCulture)) + "," +
                                            (durn * 12).ToString() + "," + (an * 12 + luna - (ani * 12 + lunai)).ToString() + ",0)";
                                            lastCommand = cmd.CommandText;
                                            if (ano * 100 + lunao >= an * 100 + luna && valinv != vala)
                                                cmd.ExecuteNonQuery();
                                        }
                                    }
                                    if (!dr.IsClosed)
                                        dr.Close();
                                }

                            }
                            catch(SqlException ex)
                            {
                                if(ex.Number!=2627)
                                    MyTrace(lastCommand + "\n" + ex.ToString());
                            }
                            catch (Exception ex)
                            {
                                MyTrace(lastCommand + "\n" + ex.ToString() + "\n Nr. inventar" + Nrinv.ToString());
                            }
                            
                            pBar.Value++;

                        }
                        dbCont.Close();
                    }
                    
                    
                    dBaseCommand = new System.Data.OleDb.OleDbCommand("SELECT * FROM [" + nume_fis_doc + "]", dBaseConnection);
                    dbCont = dBaseCommand.ExecuteReader();
                    if (dbCont != null)
                    {
                        //daca are o inregistrare
                        pBar.Maximum = dbCont.RecordsAffected;
                        pBar.Value = 0;
                        while (dbCont.Read())
                        {
                            if (dbCont["nrinv"].ToString() == String.Empty)
                                break;
                            Nrinv = Convert.ToInt32(dbCont["nrinv"]);
                            den = dbCont["den"].ToString().Trim();
                            grupa = dbCont["grupa"].ToString().Trim();
                            subgrupa = dbCont["subgrupa"].ToString().Trim();
                            clasa = dbCont["clasa"].ToString().Trim();
                            subclasa = dbCont["subclasa"].ToString().Trim();
                            familia = dbCont["familia"].ToString().Trim();
                           
                            sec = Convert.ToInt16(dbCont["seci"]); //sectia
                            durn = Convert.ToInt32(dbCont["durn"]);
                            valinv = Convert.ToDecimal(dbCont["valinv"]);
                            vala = Convert.ToDecimal(dbCont["vala"]);
                            sint = dbCont["conts"].ToString();
                            an1 = dbCont["contan1"].ToString().Trim();
                            an2 = dbCont["contan2"].ToString().Trim();
                            String tip = dbCont["tip"].ToString().Trim();
                            if (Convert.ToInt16(an1) == 0)
                                an1 = String.Empty;
                            if (Convert.ToInt16(an2) == 0)
                                an2 = String.Empty;
                            String numeC = sint + (an1 != String.Empty ? "." + an1 : String.Empty) + (an2 != String.Empty ? "." + an2 : String.Empty);
                            regim = Convert.ToInt16(dbCont["regim"]); //sectia

                            cmd.CommandText = "SELECT * FROM [AmixCatalog] WHERE [Grupa]=" + grupa + " AND [SubGrupa] = " + subgrupa +
                                " AND [Clasa]=" + clasa + " AND [SubClasa]=" + subclasa;
                            if (familia != String.Empty)
                                cmd.CommandText += " AND [Familia]='" + familia + "'";
                            SqlDataReader dr = cmd.ExecuteReader();
                            if (dr != null)
                            {
                                if (dr.Read())
                                {
                                    idclas = Convert.ToInt32(dr["IDClasificare"]);
                                }
                                dr.Close();
                            }
                            


                            cmd.CommandText = "INSERT INTO [AmixDocumente] ([NrInventar],[Denumire],[IdClasificare],[IDDepartament],[ContAmortizare],[ValoareInventar]" +
                                    ",[DurataAmortizare],[RegimAmortizare],[DataDoc],[NumarDoc],[ComisiaRec],[DurExtindere],[CodUnitate],[IDMod],[IDTipDocument],[ContCheltuiala],[ContImobilizare]" +
                                    ",[Luna],[Anul],[AmortizareLuna],[AmortizareAn],[ValoareAmortizata],[ContTransfer],[IDMijlocFix],[PunereInFunctiune],[Compus],[AmortizarePrimulAn],[IDGestionar],[ValoareSuplimentara],[LuniAmortizate],[IDBK]) VALUES ('"
                                 + Nrinv.ToString() + "','" + den + "'," + idclas.ToString() + "," + (sec + 10000).ToString() + ",NULL," + Convert.ToDouble(dbCont["valinv"]).ToString("F2", CultureInfo.InvariantCulture) + "," + 
                                (durn * 12).ToString() + "," + regim.ToString() + ",'" + an.ToString() + "-" + luna.ToString() + "-01',0,'',0," +
                                ModuleInfo.CodUnitate.ToString() + ",0,";
                            if (tip == "i")
                                cmd.CommandText += "2,'";
                            else if (tip == "m")
                                cmd.CommandText += "8,'";
                            else if (tip == "t")
                                cmd.CommandText += "4,'";
                            else if (tip == "o")
                                cmd.CommandText += "3,'";
                            DateTime dpf = new DateTime(an,luna,1);
                            dpf = dpf.AddMonths(-1);
                            cmd.CommandText += numeC + "',NULL," + luna.ToString() + "," + an.ToString() + "," + Convert.ToDouble(dbCont["ra"]).ToString("F2", CultureInfo.InvariantCulture) + "," +
                                (Convert.ToDouble(dbCont["ra"]) * 12).ToString("F2", CultureInfo.InvariantCulture) + "," + Convert.ToDouble(dbCont["vala"]).ToString("F2", CultureInfo.InvariantCulture) + ",NULL," + Nrinv.ToString() + ",'" + dpf.Year.ToString() + "-" + dpf.Month.ToString() + "-01',0,0,1," +
                                Convert.ToDouble(dbCont["valinv"]).ToString("F2", CultureInfo.InvariantCulture) + "," + (an * 12 + luna - (ani * 12 + lunai)).ToString() + ",NULL)";
                            lastCommand = cmd.CommandText;
                            cmd.ExecuteNonQuery();

                            pBar.Value++; // CultureInfo.InvariantCulture
                        }
                    }


                    dbCont.Close();
                    //tran.Commit();
                }
                catch (Exception ex)
                {
                    MyTrace(lastCommand + "\n" + ex.ToString() + "\n Nr. inventar" + dbCont["nrinv"].ToString());
                    return;
                }
                finally
                {
                    if (dbCont != null)
                        dbCont.Close();
                    dBaseConnection.Close();
                }
            }

        }

        private void Import(object sender, EventArgs e)
        {
            LunaCurenta = PerioadaCurentaDTP.Value.Month;
            AnulCurent = PerioadaCurentaDTP.Value.Year;


            if (ImportTC.SelectedTab == SqlTP)
            {
                Import_SQL(sender, e);
                return;
            }
            

            TreeView tv = FisiereTV;
            if (ImportTC.SelectedTab == MagnumTP)
                tv = FisiereMagnumTV;
            

            if(tv.SelectedNode == null || ((FileNodeInfo)tv.SelectedNode.Tag) == null || !((FileNodeInfo)tv.SelectedNode.Tag).seImporta) 
            {
                if (tv.SelectedNode == null)
                    InCalea();
                MessageBox.Show("Va rugam selectati o cale valida pentru import!",
                        "Eroare",
                        MessageBoxButtons.OK,
                        MessageBoxIcon.Stop);
                return;
            }

            if (ImportTC.SelectedTab != MagnumTP && MessageBox.Show("Atentie datele din unitatea curenta se vor suprascrie! Continuati?",
                    "Avertisment",
                    MessageBoxButtons.YesNo,
                    MessageBoxIcon.Stop) == DialogResult.No)
                return;
            FileNodeInfo fin = tv.SelectedNode.Tag as FileNodeInfo;
            SqlConnection con = DeschideConexiune(workConnString);
            //se incepe din perioada curenta
            //importa departamentele
            if (ImportTC.SelectedTab == MagnumTP)
                ImportMagnum(fin.NumeUnitate, tv.SelectedNode.Text, con, fin.tipuri);
            else
                if (ImportTC.SelectedTab == AmixTP)
                {
                    if (!InArhivaCB.Checked)
                    {

                        if (!ImportDepartament(fin.NumeUnitate, con))
                            return;

                        if (!ImportMix(fin.NumeUnitate, con))
                            return;

                        if (!ImportDocumente(fin.NumeUnitate, con))
                            return;

                        //prelucreaza directoarelee subordonate
                        foreach (TreeNode nod in tv.SelectedNode.Nodes)
                        {
                            FileNodeInfo infoCont = nod.Tag as FileNodeInfo;
                            {
                                if (infoCont.NumeUnitate[infoCont.NumeUnitate.Length - 1] != '\\')
                                    infoCont.NumeUnitate += "\\";
                                ImportArhive(infoCont.NumeUnitate, infoCont.An, con);
                                //ImportDocumenteArhive(infoCont.NumeUnitate, infoCont.An, con);
                            }

                        }
                    }
                    else
                    {
                        /// verifica daca avem arhivata luna respectiva
                        /// 
                        SqlCommand cmdVer = new SqlCommand("SELECT COUNT(*) FROM [AmixRegistruBK] WHERE Luna=" + LunaCurenta.ToString() + " AND  An =" + AnulCurent.ToString(), con);
                        Object ret = cmdVer.ExecuteScalar();
                        if (Convert.ToInt32(ret) == 0)
                        { 
                            ///copiaza din perioada curenta in arhiva
                            cmdVer.CommandText = "INSERT INTO [AmixRegistruBK] "+
			                    "([IDMijlocFix],[NrInventar],[Denumire],[ContAmortizare],[StocFaptic],[IDClasificare],[DataIntrare],[DataIesire],[CodUnitate],[IDDepartament],[IDStare] " +
                                ",[ContImobilizare],[ContCheltuiala],[DurataAmortizare],[RegimAmortizare],[ValoareInventar],[PunereInFunctiune],[Compus] ,[Blocat]" +
				                ",[IDGestionar],[ContTransfer],[ValoareAmortizata],[LuniAmortizate],[AmortizareLuna],[AmortizareAn],[InFolosinta],[CantitateOI]"+
				                ",[Categorie],[IDUnitateMasura],Luna,An)"+
				                "SELECT [IDMijlocFix],[NrInventar],[Denumire],[ContAmortizare],[StocFaptic],[IDClasificare],[DataIntrare],[DataIesire],[CodUnitate],[IDDepartament],[IDStare]"+
				                ",[ContImobilizare],[ContCheltuiala],[DurataAmortizare],[RegimAmortizare],[ValoareInventar],[PunereInFunctiune],[Compus] ,[Blocat]"+
				                ",[IDGestionar],[ContTransfer],[ValoareAmortizata],[LuniAmortizate],[AmortizareLuna],[AmortizareAn],[InFolosinta],[CantitateOI] "+
				                ",[Categorie],[IDUnitateMasura],"+LunaCurenta.ToString() + "," + AnulCurent.ToString() +
			                    "FROM [AmixRegistru] a "+
			                    "WHERE  [CodUnitate]=" + ModuleInfo.CodUnitate.ToString();
                            cmdVer.ExecuteNonQuery();
                        }
                        if (!ImportMixArhiva(fin.NumeUnitate, con))
                            return;


                    }

                }
            InchideConexiune(con);
            MessageBoxFrm mbt = new MessageBoxFrm("Mesaj", "Importul datelor s-a terminat!", String.Empty, MessageBoxFrm.ImaginiMB.SeLucreazaIcon, null);
            mbt.AddButton("Iesire", 70, mbt.MessageBoxFrm_Click_Yes);
            mbt.ShowDialog();
        }

        private Boolean ImportDocumente(String Vcale, SqlConnection con)
        {
            pBar.Value = 0;
            String Cont_Parinte = String.Empty, den = String.Empty, grupa = String.Empty, subgrupa = String.Empty, clasa = String.Empty, subclasa = String.Empty, familia = String.Empty,
                sint = String.Empty, an1 = String.Empty, an2 = String.Empty;
            Int32 Nrinv = 0;
            Int32 idclas = 0, durn = 0;
            Int16 ani = 0, lunai = 0, ano = 0, lunao = 0, sec = 0, regim = 0;
            System.Data.OleDb.OleDbConnection dBaseConnection = null;
            System.Data.OleDb.OleDbDataReader dbCont = null;

            SqlTransaction tran = con.BeginTransaction();
            SqlCommand cmd = new SqlCommand("", con);
            cmd.CommandTimeout = 180;
            cmd.Transaction = tran;

            String cale = Vcale.Replace("ARHIVA", String.Empty);
            String ConnectionString = "Provider=VFPOLEDB.1;Data Source = " + cale + ";";
            dBaseConnection = new System.Data.OleDb.OleDbConnection(ConnectionString);
            dBaseConnection.Open();
            OleDbCommand dBaseCommand = new System.Data.OleDb.OleDbCommand("", dBaseConnection);
            dBaseCommand.CommandText = "SELECT * FROM [Mis]";
            try
            {
                dbCont = dBaseCommand.ExecuteReader();
                if (dbCont == null)
                {
                    return true;
                }
                //daca are o inregistrare
                Decimal valinv = Decimal.Zero, vala = Decimal.Zero;
                pBar.Maximum = dbCont.RecordsAffected;
                while (dbCont.Read())
                {
                    Nrinv = Convert.ToInt32(dbCont["nrinv"]);
                    den = dbCont["den"].ToString().Trim();
                    grupa = dbCont["grupa"].ToString().Trim();
                    subgrupa = dbCont["subgrupa"].ToString().Trim();
                    clasa = dbCont["clasa"].ToString().Trim();
                    subclasa = dbCont["subclasa"].ToString().Trim();
                    familia = dbCont["familia"].ToString().Trim();
                    ani = Convert.ToInt16(dbCont["ani"]);
                    ano = Convert.ToInt16(dbCont["ano"]);
                    lunai = Convert.ToInt16(dbCont["lni"]);
                    DateTime di = new DateTime(ani, lunai, 1);
                    DateTime dpf = di.AddMonths(-1);
                    lunao = Convert.ToInt16(dbCont["lno"]);
                    sec = Convert.ToInt16(dbCont["seci"]); //sectia
                    durn = Convert.ToInt32(dbCont["durn"]);
                    di = di.AddYears(durn);
                    valinv = Convert.ToDecimal(dbCont["valinv"]);
                    vala = Convert.ToDecimal(dbCont["vala"]);
                    sint = dbCont["conts"].ToString();
                    an1 = dbCont["contan1"].ToString().Trim();
                    an2 = dbCont["contan2"].ToString().Trim();
                    String tip = dbCont["tip"].ToString().Trim();
                    if (Convert.ToInt16(an1) == 0)
                        an1 = String.Empty;
                    if (Convert.ToInt16(an2) == 0)
                        an2 = String.Empty;
                    String numeC = sint + (an1 != String.Empty ? "." + an1 : String.Empty) + (an2 != String.Empty ? "." + an2 : String.Empty);
                    regim = Convert.ToInt16(dbCont["regim"]); //sectia


                    cmd.CommandText = "SELECT * FROM [AmixCatalog] WHERE [Grupa]=" + grupa + " AND [SubGrupa] = " + subgrupa +
                        " AND [Clasa]=" + clasa + " AND [SubClasa]=" + subclasa;
                    if (familia != String.Empty)
                        cmd.CommandText += " AND [Familia]='" + familia + "'";
                    SqlDataReader dr = cmd.ExecuteReader();
                    if (dr != null)
                    {
                        if (dr.Read())
                        {
                            idclas = Convert.ToInt32(dr["IDClasificare"]);
                        }
                        dr.Close();
                    }


                    cmd.CommandText = "INSERT INTO [AmixDocumente] ([NrInventar],[Denumire],[IdClasificare],[IDDepartament],[ContAmortizare],[ValoareInventar]" +
                            ",[DurataAmortizare],[RegimAmortizare],[DataDoc],[NumarDoc],[ComisiaRec],[DurExtindere],[CodUnitate],[IDMod],[IDTipDocument],[ContCheltuiala],[ContImobilizare]" +
                            ",[Luna],[Anul],[AmortizareLuna],[AmortizareAn],[ValoareAmortizata],[ContTransfer],[IDMijlocFix],[PunereInFunctiune],[Compus],[AmortizarePrimulAn],[IDGestionar],[ValoareSuplimentara],[LuniAmortizate],[IDBK]) VALUES ('" 
                         + Nrinv.ToString() + "','" + den + "'," + idclas.ToString() + "," + (sec + 10000).ToString() +  ",NULL," + Convert.ToDouble(dbCont["valinv"]).ToString("F2", CultureInfo.InvariantCulture) +
                        (durn * 12).ToString() + "," + regim.ToString() + ",'" + AnulCurent.ToString() + "-" + LunaCurenta.ToString() + "-01',0,'',0" +
                        this.ModuleInfo.CodUnitate.ToString() + ",0,";
                    if (tip == "i")
                        cmd.CommandText += "2,";
                    else if (tip == "m")
                        cmd.CommandText += "8,";
                    else if (tip == "t")
                        cmd.CommandText += "4,";
                    else if (tip == "o")
                        cmd.CommandText += "3,";
                    cmd.CommandText += numeC + ",NULL," + LunaCurenta.ToString() + "," + AnulCurent.ToString() + "," + Convert.ToDouble(dbCont["ra"]).ToString("F2", CultureInfo.InvariantCulture) + "," +
                        (Convert.ToDouble(dbCont["ra"]) * 12).ToString("F2", CultureInfo.InvariantCulture) + "," + Convert.ToDouble(dbCont["vala"]).ToString("F2", CultureInfo.InvariantCulture) + ",NULL," + Nrinv.ToString() + ",'" + dpf.Year.ToString() + "-" + dpf.Month.ToString() + "-01',0,0,1," +
                        Convert.ToDouble(dbCont["valinv"]).ToString("F2", CultureInfo.InvariantCulture) + "," + (AnulCurent * 12 + LunaCurenta - (ani * 12 + lunai)).ToString() + ",NULL)";
                    cmd.ExecuteNonQuery();

                    pBar.Value++; // CultureInfo.InvariantCulture
                }


                tran.Commit();
            }
            catch (Exception ex)
            {

                MessageBox.Show(ex.Message.ToString() + ",\n" + cmd.CommandText);
                tran.Rollback();
                return false;
            }
            finally
            {
                if (dbCont != null)
                    dbCont.Close();
                dBaseConnection.Close();
            }


            dbCont.Close();
            dBaseConnection.Close();
            return true;

        }


        private Boolean ImportMix(String Vcale, SqlConnection con)
        {
            pBar.Value = 0;
            String Cont_Parinte = String.Empty, den = String.Empty, grupa = String.Empty,subgrupa  = String.Empty, clasa = String.Empty,subclasa = String.Empty,familia = String.Empty,
                sint=String.Empty,an1=String.Empty,an2=String.Empty;
            Int32 Nrinv = 0;
            Int32 idclas=0,durn=0;
            Int16 ani=0, lunai=0, ano=0, lunao=0,sec=0,regim=0;
            System.Data.OleDb.OleDbConnection dBaseConnection = null;
            System.Data.OleDb.OleDbDataReader dbCont = null;

            SqlTransaction tran = con.BeginTransaction();
            SqlCommand cmd = new SqlCommand("", con);
            cmd.CommandTimeout = 180;
            cmd.Transaction = tran;

            String cale = Vcale.Replace("ARHIVA", String.Empty);
            String ConnectionString = "Provider=VFPOLEDB.1;Data Source = " + cale + ";";
            dBaseConnection = new System.Data.OleDb.OleDbConnection(ConnectionString);
            dBaseConnection.Open();
            OleDbCommand dBaseCommand = new System.Data.OleDb.OleDbCommand("", dBaseConnection);
            dBaseCommand.CommandText = "SELECT * FROM [Mix]";
            try
            {

                dbCont = dBaseCommand.ExecuteReader();
                if (dbCont == null)
                {
                    return true;
                }
                //daca are o inregistrare

                cmd.CommandText = "SET IDENTITY_INSERT [AmixRegistru] ON";
                cmd.ExecuteScalar();
                Decimal valinv = Decimal.Zero, vala = Decimal.Zero;
                pBar.Maximum = dbCont.RecordsAffected;
                while (dbCont.Read())
                {
                    Nrinv = Convert.ToInt32(dbCont["nrinv"]);
                    den = dbCont["den"].ToString().Trim();
                    grupa = dbCont["grupa"].ToString().Trim();
                    subgrupa = dbCont["subgrupa"].ToString().Trim();
                    clasa = dbCont["clasa"].ToString().Trim();
                    subclasa = dbCont["subclasa"].ToString().Trim();
                    familia = dbCont["familia"].ToString().Trim();
                    ani = Convert.ToInt16(dbCont["ani"]);
                    ano = Convert.ToInt16(dbCont["ano"]);
                    lunai = Convert.ToInt16(dbCont["lni"]);
                    DateTime di = new DateTime(ani,lunai,1);
                    DateTime dpf = di.AddMonths(-1);
                    lunao = Convert.ToInt16(dbCont["lno"]);
                    sec = Convert.ToInt16(dbCont["sec"]); //sectia
                    durn = Convert.ToInt32(dbCont["durn"]);
                    di = di.AddYears(durn);
                    valinv = Convert.ToDecimal(dbCont["valinv"]);
                    vala = Convert.ToDecimal(dbCont["vala"]);
                    sint = dbCont["conts"].ToString();
                    an1 = dbCont["contan1"].ToString().Trim();
                    an2 = dbCont["contan2"].ToString().Trim();
                    if (Convert.ToInt16(an1) == 0)
                        an1 = String.Empty;
                    if (Convert.ToInt16(an2) == 0)
                        an2 = String.Empty;
                    String numeC = sint + (an1 != String.Empty ? "." + an1 : String.Empty) + (an2 != String.Empty ? "." + an2 : String.Empty);


                    #region Verificare existenta cont cheltuiala la mijlocaele fixe
                    //verificare existenta cont sintetic
                    cmd.CommandText = "SELECT COUNT(*) FROM [Conturi] WHERE [CodUnitate]=" + ModuleInfo.CodUnitate.ToString() + " AND [Cont]='"
                        + numeC + "'";
                    Object cate = cmd.ExecuteScalar();
                    if (Convert.ToInt16(cate) == 0)
                    {
                        //verificare existenta cont sintetic
                        cmd.CommandText = "SELECT COUNT(*) FROM [Conturi] WHERE [CodUnitate]=" + ModuleInfo.CodUnitate.ToString() + " AND [Cont]='"
                            + sint + "'";
                        cate = cmd.ExecuteScalar();
                        SqlCommand cntcom = null;
                        if (Convert.ToInt16(cate) == 0)
                        {
                            //adaugam contul sintetic
                            cntcom = new SqlCommand("INSERT INTO [Conturi] (Cont,CodUnitate,Denumire,ContParinte) VALUES ('" +
                                       sint + "'," + ModuleInfo.CodUnitate.ToString() + ",'Cont cheltuiala mijloace fixe (generat automat)','N/A')",
                                       cmd.Connection);
                            cntcom.Transaction = tran;
                            cntcom.CommandTimeout = 180;
                            cmd.CommandText = cntcom.CommandText;
                            cntcom.ExecuteNonQuery();
                        }
                        //adaugam contul sintetic

                        if (numeC != sint)
                        {
                            cntcom = new SqlCommand("INSERT INTO [Conturi] (Cont,CodUnitate,Denumire,ContParinte) VALUES ('" +
                                       numeC + "'," + ModuleInfo.CodUnitate.ToString() + ",'Cont cheltuiala mijloace fixe (generat automat)','" + sint + "')",
                                       cmd.Connection);
                            cntcom.Transaction = tran;
                            cntcom.CommandTimeout = 180;
                            cmd.CommandText = cntcom.CommandText;
                            cntcom.ExecuteNonQuery();
                        }
                    } 
                    #endregion
                    

                    regim = Convert.ToInt16(dbCont["regim"]); 


                    cmd.CommandText = "SELECT * FROM [AmixCatalog] WHERE [Grupa]=" + grupa + " AND [SubGrupa] = " + subgrupa +
                        " AND [Clasa]=" + clasa + " AND [SubClasa]=" + subclasa;
                    if(familia!=String.Empty)
                        cmd.CommandText += " AND [Familia]='" + familia + "'";
                    SqlDataReader dr = cmd.ExecuteReader();
                    if (dr != null)
                    {
                        if (dr.Read())
                        {
                            idclas = Convert.ToInt32(dr["IDClasificare"]);
                        }
                        dr.Close();
                    }


                    cmd.CommandText = "INSERT INTO [AmixRegistru] ([IDMijlocFix],[NrInventar],[Denumire],[ContAmortizare],[StocFaptic],[IDClasificare]," +
                        "[DataIntrare],[DataIesire],[CodUnitate],[IDDepartament],[IDStare],[ContImobilizare],[ContCheltuiala],[DurataAmortizare],[RegimAmortizare],[ValoareInventar]" +
                        ",[PunereInFunctiune],[Compus],[Blocat],[IDGestionar],[ContTransfer],[ValoareAmortizata],[LuniAmortizate],[AmortizareLuna],[AmortizareAn]) VALUES (" +
                        Nrinv.ToString() + ",'" + Nrinv.ToString() + "','" + den + "',NULL,1," + idclas.ToString() + ",'" + ani.ToString() + "-" + lunai.ToString() + "-01','" +
                        (ano == 0 ? di.Year.ToString() + "-" + di.Month.ToString() : ano.ToString() + "-" + lunao.ToString()) + "-01'," +
                        this.ModuleInfo.CodUnitate.ToString() + ","/*iddepartament*/ + (sec + 10000).ToString() + ",";

                    //daca nu e casat e in evidenta
                    //IdStare daca data iesirii e completata
                    if ((ano * 100 + lunao <= AnulCurent * 100 + LunaCurenta) && ano != 0 && lunao != 0)
                    {
                        if(valinv >= vala)
                            cmd.CommandText += "5,"; //daca mai am de amortizat il trec in evidenta
                        else
                            cmd.CommandText += "1,"; //amortizat nu iesit iesit
                    }
                    else
                        if (valinv == vala)
                            cmd.CommandText += "1,"; //amortizat
                        else
                            cmd.CommandText += "5,"; //evidenta

                    cmd.CommandText += "NULL,'" + numeC + "'," + (durn * 12).ToString() + "," + regim.ToString() + "," + Convert.ToDouble(dbCont["valinv"]).ToString("F2", CultureInfo.InvariantCulture) + ",'" + dpf.Year.ToString() + "-" + dpf.Month.ToString() + "-01',0,0,1,NULL," +
                        vala.ToString("G", (CultureInfo.InvariantCulture)) + "," + (AnulCurent * 12 + LunaCurenta - (ani * 12 + lunai)).ToString() + "," + Convert.ToDouble(dbCont["ra"]).ToString("F2", CultureInfo.InvariantCulture) + "," + Convert.ToDouble(dbCont["amoan"]).ToString("F2", CultureInfo.InvariantCulture) + ")";
                    cmd.ExecuteNonQuery();

                    //fara amortizare - trebuie adugate amortizare ca sa apara in lista
                    if (regim != 0 || (ano == 0 && lunao == 0 && regim==0)) //daca mai avem ce amortiza
                    {
                        cmd.CommandText = "INSERT INTO [AmixAmortizare] ([IDMijlocFix],[Luna],[An],[ValoareInventar],[ValoareAmortizata],[ValoareRamasa],[AmortizareAn],[AmortizareLuna]" +
                            ",[DurataNormata],[LuniAmortizate],[Blocat]) VALUES (" +
                            Nrinv.ToString() + "," + LunaCurenta.ToString() + "," + AnulCurent.ToString() + "," + Convert.ToDouble(dbCont["valinv"]).ToString("F2", CultureInfo.InvariantCulture) + "," +
                            Convert.ToDouble(dbCont["vala"]).ToString("F2", CultureInfo.InvariantCulture) + "," + Convert.ToDouble(valinv - vala).ToString("F2", CultureInfo.InvariantCulture) + "," +
                            Convert.ToDouble(dbCont["amoan"]).ToString("F2", CultureInfo.InvariantCulture) + "," + Convert.ToDouble(dbCont["ra"]).ToString("F2", CultureInfo.InvariantCulture) + "," +
                            (durn * 12).ToString() + "," + (AnulCurent * 12 + LunaCurenta - (ani * 12 + lunai)).ToString() + ",0)";
                        if ((ano * 100 + lunao >= AnulCurent * 100 + LunaCurenta /*&& valinv != vala*/ && ano != 0 && lunao != 0)
                            || valinv >= vala)
                        {
                            cmd.ExecuteNonQuery();
                            Decimal ra = Convert.ToDecimal(dbCont["ra"]);
                            if (ra > 0)
                            {
                                Decimal vali = Convert.ToDecimal(dbCont["valinv"]);
                                Decimal valam = Convert.ToDecimal(dbCont["vala"]);
                                
                                Int32 amol = ano * 12 + lunao - ani * 12 - lunai - AnulCurent * 12 - LunaCurenta + ani * 12 + lunai;
                                Int32 maitrebuie = Convert.ToInt32(Decimal.Ceiling((vali - valam) / ra)) - amol;
                                if (maitrebuie > 0)
                                {
                                    Int32 per = ano * 12 + lunao + maitrebuie;
                                    Int32 duramor = ano * 12 + lunao + maitrebuie - ani*12 - lunai;
                                    if (per > 0)
                                    {
                                        cmd.CommandText = "UPDATE [AmixRegistru] SET [DataIesire]='" + (per / 12).ToString() + "-" + (per % 12 == 0 ? 12 : per % 12).ToString() + "-01',"+
                                        "[DurataAmortizare]=" + duramor .ToString()+ ",[IDStare]=5 WHERE [IDMijlocFix]=" + Nrinv.ToString();
                                        cmd.ExecuteNonQuery();
                                    }
                                }
                            }
                        }
                    }
                    else
                        MyTrace("Nr.inv:" + Nrinv.ToString() + " regim: " + regim.ToString() + " an: " + ano.ToString() + "." + lunao.ToString()); 

                    pBar.Value++; // CultureInfo.InvariantCulture
                }

                cmd.CommandText = "SET IDENTITY_INSERT [AmixRegistru] OFF";
                cmd.ExecuteScalar();

                tran.Commit();
            }
            catch (Exception ex)
            {

                MessageBox.Show(ex.Message.ToString() + ",\n" + cmd.CommandText);
                tran.Rollback();
                return false;
            }
            finally
            {
                if (dbCont != null)
                    dbCont.Close();
                dBaseConnection.Close();
            }


            dbCont.Close();
            dBaseConnection.Close();
            return true;

        }

        private Boolean ImportMixArhiva(String Vcale, SqlConnection con)
        {
            pBar.Value = 0;
            String Cont_Parinte = String.Empty, den = String.Empty, grupa = String.Empty, subgrupa = String.Empty, clasa = String.Empty, subclasa = String.Empty, familia = String.Empty,
                sint = String.Empty, an1 = String.Empty, an2 = String.Empty;
            Int32 Nrinv = 0;
            Int32 idclas = 0, durn = 0;
            Int16 ani = 0, lunai = 0, ano = 0, lunao = 0, sec = 0, regim = 0;
            System.Data.OleDb.OleDbConnection dBaseConnection = null;
            System.Data.OleDb.OleDbDataReader dbCont = null;

            SqlTransaction tran = con.BeginTransaction();
            SqlCommand cmd = new SqlCommand("", con);
            cmd.CommandTimeout = 180;
            cmd.Transaction = tran;

            String cale = Vcale.Replace("ARHIVA", String.Empty);
            String ConnectionString = "Provider=VFPOLEDB.1;Data Source = " + cale + ";";
            dBaseConnection = new System.Data.OleDb.OleDbConnection(ConnectionString);
            dBaseConnection.Open();
            OleDbCommand dBaseCommand = new System.Data.OleDb.OleDbCommand("", dBaseConnection);
            dBaseCommand.CommandText = "SELECT * FROM [Mix]";
            try
            {

                dbCont = dBaseCommand.ExecuteReader();
                if (dbCont == null)
                {
                    return true;
                }
                //daca are o inregistrare

                Decimal valinv = Decimal.Zero, vala = Decimal.Zero;
                pBar.Maximum = dbCont.RecordsAffected;
                while (dbCont.Read())
                {
                    Nrinv = Convert.ToInt32(dbCont["nrinv"]);
                    den = dbCont["den"].ToString().Trim();
                    grupa = dbCont["grupa"].ToString().Trim();
                    subgrupa = dbCont["subgrupa"].ToString().Trim();
                    clasa = dbCont["clasa"].ToString().Trim();
                    subclasa = dbCont["subclasa"].ToString().Trim();
                    familia = dbCont["familia"].ToString().Trim();
                    ani = Convert.ToInt16(dbCont["ani"]);
                    ano = Convert.ToInt16(dbCont["ano"]);
                    lunai = Convert.ToInt16(dbCont["lni"]);
                    DateTime di = new DateTime(ani, lunai, 1);
                    DateTime dpf = di.AddMonths(-1);
                    lunao = Convert.ToInt16(dbCont["lno"]);
                    sec = Convert.ToInt16(dbCont["sec"]); //sectia
                    durn = Convert.ToInt32(dbCont["durn"]);
                    di = di.AddYears(durn);
                    valinv = Convert.ToDecimal(dbCont["valinv"]);
                    vala = Convert.ToDecimal(dbCont["vala"]);
                    sint = dbCont["conts"].ToString();
                    an1 = dbCont["contan1"].ToString().Trim();
                    an2 = dbCont["contan2"].ToString().Trim();
                    if (Convert.ToInt16(an1) == 0)
                        an1 = String.Empty;
                    if (Convert.ToInt16(an2) == 0)
                        an2 = String.Empty;
                    String numeC = sint + (an1 != String.Empty ? "." + an1 : String.Empty) + (an2 != String.Empty ? "." + an2 : String.Empty);


                    #region Verificare existenta cont cheltuiala la mijlocaele fixe
                    //verificare existenta cont sintetic
                    cmd.CommandText = "SELECT COUNT(*) FROM [Conturi] WHERE [CodUnitate]=" + ModuleInfo.CodUnitate.ToString() + " AND [Cont]='"
                        + numeC + "'";
                    Object cate = cmd.ExecuteScalar();
                    if (Convert.ToInt16(cate) == 0)
                    {
                        //verificare existenta cont sintetic
                        cmd.CommandText = "SELECT COUNT(*) FROM [Conturi] WHERE [CodUnitate]=" + ModuleInfo.CodUnitate.ToString() + " AND [Cont]='"
                            + sint + "'";
                        cate = cmd.ExecuteScalar();
                        SqlCommand cntcom = null;
                        if (Convert.ToInt16(cate) == 0)
                        {
                            //adaugam contul sintetic
                            cntcom = new SqlCommand("INSERT INTO [Conturi] (Cont,CodUnitate,Denumire,ContParinte) VALUES ('" +
                                       sint + "'," + ModuleInfo.CodUnitate.ToString() + ",'Cont cheltuiala mijloace fixe (generat automat)','N/A')",
                                       cmd.Connection);
                            cntcom.Transaction = tran;
                            cntcom.CommandTimeout = 180;
                            cmd.CommandText = cntcom.CommandText;
                            cntcom.ExecuteNonQuery();
                        }
                        //adaugam contul sintetic

                        if (numeC != sint)
                        {
                            cntcom = new SqlCommand("INSERT INTO [Conturi] (Cont,CodUnitate,Denumire,ContParinte) VALUES ('" +
                                       numeC + "'," + ModuleInfo.CodUnitate.ToString() + ",'Cont cheltuiala mijloace fixe (generat automat)','" + sint + "')",
                                       cmd.Connection);
                            cntcom.Transaction = tran;
                            cntcom.CommandTimeout = 180;
                            cmd.CommandText = cntcom.CommandText;
                            cntcom.ExecuteNonQuery();
                        }
                    }
                    #endregion


                    regim = Convert.ToInt16(dbCont["regim"]);


                    cmd.CommandText = "SELECT * FROM [AmixCatalog] WHERE [Grupa]=" + grupa + " AND [SubGrupa] = " + subgrupa +
                        " AND [Clasa]=" + clasa + " AND [SubClasa]=" + subclasa;
                    if (familia != String.Empty)
                        cmd.CommandText += " AND [Familia]='" + familia + "'";
                    SqlDataReader dr = cmd.ExecuteReader();
                    if (dr != null)
                    {
                        if (dr.Read())
                        {
                            idclas = Convert.ToInt32(dr["IDClasificare"]);
                        }
                        dr.Close();
                    }


                    cmd.CommandText = "UPDATE [AmixRegistruBK] SET " +
                        
                        "[Denumire] = '"+ den + "',[StocFaptic]=1,"+
                        "[IDClasificare] = "+idclas.ToString()+"," +
                        "[DataIntrare]='" + ani.ToString() + "-" + lunai.ToString() + "-01',"+
                        "[DataIesire]='"+(ano == 0 ? di.Year.ToString() + "-" + di.Month.ToString() : ano.ToString() + "-" + lunao.ToString()) + "-01',"+
                        "[IDStare]=";
                        //daca nu e casat e in evidenta
                        //IdStare daca data iesirii e completata
                        if ((ano * 100 + lunao <= AnulCurent * 100 + LunaCurenta) && ano != 0 && lunao != 0)
                        {
                            if (valinv >= vala)
                                cmd.CommandText += "5,"; //daca mai am de amortizat il trec in evidenta
                            else
                                cmd.CommandText += "1,"; //amortizat nu iesit iesit
                        }
                        else
                            if (valinv == vala)
                                cmd.CommandText += "1,"; //amortizat
                            else
                                cmd.CommandText += "5,"; //evidenta

                        cmd.CommandText +=
                            "[DurataAmortizare]=" + (durn * 12).ToString() + "," +
                            "[RegimAmortizare]=" + regim.ToString() + "," +
                            "[ValoareInventar]=" + Convert.ToDouble(dbCont["valinv"]).ToString("F2", CultureInfo.InvariantCulture) + "," +
                            "[PunereInFunctiune]='" + dpf.Year.ToString() + "-" + dpf.Month.ToString() + "-01'," +
                            "[Compus]=0,[Blocat]=0,[ValoareAmortizata]=" + vala.ToString("G", (CultureInfo.InvariantCulture)) + "," +
                            "[LuniAmortizate]=" + (AnulCurent * 12 + LunaCurenta - (ani * 12 + lunai)).ToString() + "," +
                            "[AmortizareLuna]=" + Convert.ToDouble(dbCont["ra"]).ToString("F2", CultureInfo.InvariantCulture) + "," +
                            "[AmortizareAn]=" + Convert.ToDouble(dbCont["amoan"]).ToString("F2", CultureInfo.InvariantCulture) +
                            " WHERE NrInventar=" + Nrinv.ToString() + " AND Luna=" + LunaCurenta.ToString() + " AND  An =" + AnulCurent.ToString();
                        cmd.ExecuteNonQuery();

                    
                    pBar.Value++; // CultureInfo.InvariantCulture
                }

                tran.Commit();
            }
            catch (Exception ex)
            {

                MessageBox.Show(ex.Message.ToString() + ",\n" + cmd.CommandText);
                tran.Rollback();
                return false;
            }
            finally
            {
                if (dbCont != null)
                    dbCont.Close();
                dBaseConnection.Close();
            }


            dbCont.Close();
            dBaseConnection.Close();
            return true;

        }



        private Boolean ImportDepartament(String Vcale, SqlConnection con)
        {
            pBar.Value = 0;
            String Cont_Parinte = String.Empty, den = String.Empty, an2 = String.Empty;
            Int16 cod = 0;
            System.Data.OleDb.OleDbConnection dBaseConnection = null;
            System.Data.OleDb.OleDbDataReader dbCont = null;

            SqlTransaction tran = con.BeginTransaction();
            SqlCommand cmd = new SqlCommand("", con);
            cmd.CommandTimeout = 180;
            cmd.Transaction = tran;

            String cale = Vcale.Replace("ARHIVA", String.Empty);
            String ConnectionString = "Provider=VFPOLEDB.1;Data Source = " + cale + ";";
            dBaseConnection = new System.Data.OleDb.OleDbConnection(ConnectionString);
            dBaseConnection.Open();
            OleDbCommand dBaseCommand = new System.Data.OleDb.OleDbCommand("", dBaseConnection);
            dBaseCommand.CommandText = "SELECT * FROM [Sectii]";
            try
            {
                Int32 codParinte = 0;
                Int32 idTipDepart = 0;
                //sterge tot din baza
                cmd.CommandText = "DELETE FROM [AmixAmortizare]  FROM  [AmixRegistru] b WHERE [AmixAmortizare].[IDMijlocFix] = b.[IDMijlocFix] AND b.[CodUnitate]=" + ModuleInfo.CodUnitate.ToString();
                cmd.ExecuteScalar();
                cmd.CommandText = "DELETE FROM [AmixAnsamble]  FROM  [AmixRegistru] b WHERE [AmixAnsamble].[IDMFixPrincipal] = b.[IDMijlocFix] AND b.[CodUnitate]=" + ModuleInfo.CodUnitate.ToString();
                cmd.ExecuteScalar();
                cmd.CommandText = "DELETE FROM [AmixComponenteSecundare]  FROM  [AmixRegistru] b WHERE [AmixComponenteSecundare].[IDMijlocFix] = b.[IDMijlocFix] AND b.[CodUnitate]=" + ModuleInfo.CodUnitate.ToString();
                cmd.ExecuteScalar();
                cmd.CommandText = "DELETE FROM [AmixDocumente] WHERE  [CodUnitate]=" + ModuleInfo.CodUnitate.ToString();
                cmd.ExecuteScalar();
                cmd.CommandText = "DELETE FROM [AmixRegistru] WHERE  [CodUnitate]=" + ModuleInfo.CodUnitate.ToString();
                cmd.ExecuteScalar();
                cmd.CommandText = "DELETE FROM [AmixRegistruBK] WHERE  [CodUnitate]=" + ModuleInfo.CodUnitate.ToString();
                cmd.ExecuteScalar();                
                cmd.CommandText = "DELETE FROM [Departamente] WHERE [CodUnitate]=" + ModuleInfo.CodUnitate.ToString() + " AND [IDDepartament]<>[IDDepartParinte]";
                cmd.ExecuteScalar();
                cmd.CommandText = "SELECT * FROM [Departamente] WHERE [CodUnitate]=" + ModuleInfo.CodUnitate.ToString();
                SqlDataReader dr = cmd.ExecuteReader();
                if (dr != null)
                {
                    if (dr.Read())
                    { 
                        codParinte = Convert.ToInt32(dr["IDDepartParinte"]);   
                    }
                    dr.Close();
                }
                cmd.CommandText = "SELECT * FROM [TipDepartament]";
                dr = cmd.ExecuteReader();
                if (dr != null)
                {
                    if (dr.Read())
                    {
                        idTipDepart = Convert.ToInt32(dr["IDTipDepartament"]);
                    }
                    dr.Close();
                }

                dbCont = dBaseCommand.ExecuteReader();
                if (dbCont == null)
                {
                    return true;
                }
                //daca are o inregistrare
                cmd.CommandText = "SET IDENTITY_INSERT [Departamente] ON";
                cmd.ExecuteScalar();

                pBar.Maximum = dbCont.RecordsAffected;
                while (dbCont.Read())
                {
                    cod = Convert.ToInt16(dbCont["sec"]);
                    den = dbCont["den"].ToString().Trim();

                    cmd.CommandText = "INSERT INTO [Departamente] ([IDDepartament],[Activ],[Cod],[CodUnitate],[Nume],[IDDepartParinte],[IDTipDepartament]) VALUES (" +
                        (cod+10000).ToString() + ",1,'" + cod.ToString() + "'," +
                        this.ModuleInfo.CodUnitate.ToString() + ",'" +
                        den + "'," +codParinte.ToString() + "," + idTipDepart.ToString() +  ")";
                    cmd.ExecuteNonQuery();
                    pBar.Value++; // CultureInfo.InvariantCulture
                }

                cmd.CommandText = "SET IDENTITY_INSERT [Departamente] OFF";
                cmd.ExecuteScalar();

                tran.Commit();
            }
            catch (Exception ex)
            {

                MessageBox.Show(ex.Message.ToString() + ",\n" + cmd.CommandText);
                tran.Rollback();
                return false;
            }
            finally
            {
                if (dbCont != null)
                    dbCont.Close();
                dBaseConnection.Close();
            }


            dbCont.Close();
            dBaseConnection.Close();
            return true;

        }

        private Boolean ImportCont(String Vcale, SqlConnection con)
        {
            pBar.Value = 0;
            String Cont_Parinte = String.Empty, sint = String.Empty, an1 = String.Empty, an2 = String.Empty;
            System.Data.OleDb.OleDbConnection dBaseConnection = null;
            System.Data.OleDb.OleDbDataReader dbCont = null;

            SqlTransaction tran = con.BeginTransaction();
            SqlCommand cmd = new SqlCommand("", con);


            cmd.CommandTimeout = 180;
            cmd.Transaction = tran;

            String cale = Vcale.Replace("ARHIVA", String.Empty);
            String ConnectionString = "Provider=VFPOLEDB.1;Data Source = " + cale + ";";
            dBaseConnection = new System.Data.OleDb.OleDbConnection(ConnectionString);
            dBaseConnection.Open();
            OleDbCommand dBaseCommand = new System.Data.OleDb.OleDbCommand("", dBaseConnection);
            dBaseCommand.CommandText = "SELECT * FROM [cont] ORDER BY [cont_sin],[cont_an1],[Cont_an2]";
            try
            {
                cmd.CommandText = "DELETE FROM [ConturiSpeciale] WHERE [CodUnitate]=" + ModuleInfo.CodUnitate.ToString();
                cmd.ExecuteScalar();
                cmd.CommandText = "DELETE FROM [ConturiCoresp] WHERE [CodUnitate]=" + ModuleInfo.CodUnitate.ToString();
                cmd.ExecuteScalar();
                cmd.CommandText = "DELETE FROM [RulajeDoc] WHERE [CodUnitate]=" + ModuleInfo.CodUnitate.ToString();
                cmd.ExecuteScalar();
                cmd.CommandText = "DELETE FROM [Rulaje] WHERE [CodUnitate]=" + ModuleInfo.CodUnitate.ToString();
                cmd.ExecuteScalar();
                cmd.CommandText = "DELETE FROM [Conturi] WHERE Cont<>'N/A' AND [CodUnitate]=" + ModuleInfo.CodUnitate.ToString();    
                cmd.ExecuteScalar();


                dbCont = dBaseCommand.ExecuteReader();
                if (dbCont == null)
                {
                    return true;
                }
                //daca are o inregistrare
                pBar.Maximum = dbCont.RecordsAffected;
                while (dbCont.Read())
                {
                    sint = dbCont["cont_sin"].ToString();
                    an1 = dbCont["cont_an1"].ToString().Trim();
                    an2 = dbCont["cont_an2"].ToString().Trim();
                    if (Convert.ToInt16(an1) == 0)
                        an1 = String.Empty;
                    if (Convert.ToInt16(an2) == 0)
                        an2 = String.Empty;
                    if (Convert.ToInt16(dbCont["tip_cont"]) == -1)
                        Cont_Parinte = sint;
                    String den = dbCont["expl"].ToString().Trim();
                    if (den == String.Empty)
                        den = dbCont["denumire"].ToString().Trim();
                    Int16 tip = Convert.ToInt16(dbCont["tip_cont"]);
                    String func = dbCont["functiune"].ToString().Trim();
                    String numeC = sint + (an1 != String.Empty ? "." + an1 : String.Empty) + (an2 != String.Empty ? "." + an2 : String.Empty);
                    cmd.CommandText = "INSERT INTO [Conturi] ([Cont],[CodUnitate],[Denumire],[Tip],[Terminal],[ContParinte],[EsteAnalitic],[DebitInitial],[CreditInitial],[DebitPrecedent],[CreditPrecedent],[Categorie],[RulajDebit],[RulajCredit]) VALUES ('" +
                        numeC + "'," +
                        this.ModuleInfo.CodUnitate.ToString() + ",'" +
                        den + "','" +
                        (func != "X" ? func : "A") + "'," +
                        ((tip == 1 || tip == 0) ? "1" : "0") + ",'" +
                        ((tip == -1 || tip == 0) ? "N/A" : Cont_Parinte) + "'," +
                        (tip == 1 ? "1" : "0") + "," +
                        Convert.ToDouble(dbCont["deb_init"]).ToString("F2", CultureInfo.InvariantCulture) + "," +
                        Convert.ToDouble(dbCont["cre_init"]).ToString("F2", CultureInfo.InvariantCulture) + ",0,0," +
                        (func == "X" ? "2" : "0") +
                        ",0,0)";
                    cmd.ExecuteNonQuery();
                    pBar.Value++; // CultureInfo.InvariantCulture
                }
                tran.Commit();
            }
            catch (Exception ex)
            {

                MessageBox.Show(ex.Message.ToString() + ",\n" + cmd.CommandText);
                tran.Rollback();
                return false ;
            }
            finally
            {
                if (dbCont != null)
                    dbCont.Close();
                dBaseConnection.Close();
            }


            dbCont.Close();
            dBaseConnection.Close();
            return true;
        
        }

        private Boolean ImportRulaje(String Vcale, SqlConnection con,Boolean stergeEx,Boolean arhiva,String tabela)
        {
            pBar.Value = 0;
            String Cont_Parinte = String.Empty, sintD = String.Empty, an1D = String.Empty, an2D = String.Empty, sintC = String.Empty, an1C = String.Empty, an2C = String.Empty;
            System.Data.OleDb.OleDbConnection dBaseConnection = null;
            System.Data.OleDb.OleDbDataReader dbRul = null;

            SqlTransaction tran = con.BeginTransaction();
            SqlCommand cmd = new SqlCommand("", con);
            cmd.CommandTimeout = 180;
            cmd.CommandText = "DELETE FROM [Rulaje] WHERE [CodUnitate]=" + ModuleInfo.CodUnitate.ToString();
            cmd.Transaction = tran;
            NumeFileTB.Text = Vcale + "\\" +tabela;
            String cale = String.Empty;
            if (!arhiva)
                cale = Vcale.Replace("ARHIVA", String.Empty);
            else
                cale = Vcale;
            String ConnectionString = "Provider=VFPOLEDB.1;Data Source = " + cale + ";";
            dBaseConnection = new System.Data.OleDb.OleDbConnection(ConnectionString);
            dBaseConnection.Open();
            OleDbCommand dBaseCommand = new System.Data.OleDb.OleDbCommand("", dBaseConnection);
            dBaseCommand.CommandText = "SELECT * FROM ["+
                (arhiva?tabela:"rul")+"] ORDER BY [doc],[data]";
            try
            {
                if (stergeEx)
                {
                    cmd.ExecuteScalar();
                    cmd.CommandText = "DELETE FROM [RulajeDoc] WHERE [CodUnitate]=" + ModuleInfo.CodUnitate.ToString();
                    cmd.ExecuteScalar();
                }
                dbRul = dBaseCommand.ExecuteReader();
                if (dbRul == null)
                {
                    return true;
                }
                //daca are o inregistrare
                pBar.Maximum = dbRul.RecordsAffected;
                Decimal Suma =Decimal.Zero;
                Decimal SumaValuta =Decimal.Zero;
                String CodOperatiune = String.Empty;
                Int64 IDDocument = 0;
                Int16 NrCrt = 0;
                while (dbRul.Read())
                {
                    sintD = dbRul["deb_sin"].ToString();
                    an1D = dbRul["deb_an1"].ToString().Trim();
                    an2D = dbRul["deb_an2"].ToString().Trim();

                    sintC = dbRul["cre_sin"].ToString();
                    an1C = dbRul["cre_an1"].ToString().Trim();
                    an2C = dbRul["cre_an2"].ToString().Trim();

                    if (Convert.ToInt16(an1D) == 0)
                        an1D = String.Empty;
                    if (Convert.ToInt16(an2D) == 0)
                        an2D = String.Empty;

                    if (Convert.ToInt16(an1C) == 0)
                        an1C = String.Empty;
                    if (Convert.ToInt16(an2C) == 0)
                        an2C = String.Empty;
                    String numeC = sintC + (an1C != String.Empty ? "." + an1C : String.Empty) + (an2C != String.Empty ? "." + an2C : String.Empty);
                    String numeD = sintD + (an1D != String.Empty ? "." + an1D : String.Empty) + (an2D != String.Empty ? "." + an2D : String.Empty);
                    if (numeC == "0")
                        numeC = "N/A";
                    if (numeD == "0")
                        numeD = "N/A";

                    DateTime data = Convert.ToDateTime(dbRul["data"]);
                    Int16 model = Convert.ToInt16(dbRul["model"]);
                    model -= 1;
                    if (model > 3)
                        model = 3;
                    switch (model)
                    { 
                        case 0:
                            model = 1;
                            break;
                        case 1:
                            model = 0;
                            break;
                    }
                    if (CodOperatiune != dbRul["cod_oper"].ToString().Trim())
                    {
                        if (CodOperatiune != String.Empty)
                        {
                            cmd.CommandText = "UPDATE [RulajeDoc]  SET [ValoareValuta] = " +
                              Convert.ToDouble(SumaValuta).ToString("F2", CultureInfo.InvariantCulture) + ", [ValoareLei]=" +
                              Convert.ToDouble(Suma).ToString("F2", CultureInfo.InvariantCulture) + " WHERE [IDDocument] = " + IDDocument.ToString();
                            cmd.ExecuteNonQuery();
                            
                            Suma = Decimal.Zero;
                            SumaValuta = Decimal.Zero;
                        }
                        CodOperatiune = dbRul["cod_oper"].ToString().Trim();
                        /// nu se imoprta decat inregistrari din anul curent!
                        if (data.Year != AnulCurent)
                            break;
                        /// modifica totalul la documentul anterior
                        cmd.CommandText = "INSERT INTO [RulajeDoc] ([ValoareLei],[ValoareValuta],[NumarDoc],[DataDoc],[NumarNota],[TipFormula],[IDJurnal],[Observatii],[xUser],[xHost],[xDate],[CodUnitate]) VALUES (0.00,0.00," +
                            dbRul["doc"].ToString().Trim() + ",'" +
                            data.Year.ToString() + "-" + data.Month.ToString() + "-" + data.Day.ToString() + "',''," +
                            model.ToString() + ","+
                            "0" + ",'',system_user,host_name(),getdate()," +
                            ModuleInfo.CodUnitate.ToString() + "); SELECT [IDDocument] FROM [RulajeDoc] WHERE (IDDocument = SCOPE_IDENTITY())";
                        Object ret = cmd.ExecuteScalar();
                        Trace.Assert(ret != null && ret != DBNull.Value);
                        if (ret != null && ret != DBNull.Value)
                            IDDocument = Convert.ToInt64(ret);
                    }
                    Suma += Convert.ToDecimal(dbRul["Val_finala"]);
                    SumaValuta += Convert.ToDecimal(dbRul["Val_valuta"]);
                    DateTime datai = Convert.ToDateTime(dbRul["data_doc"]);
                    cmd.CommandText = "INSERT INTO [Rulaje] ([ContDebit],[ContCredit],[CodValuta],[IDDocument],[ValoareValuta],[Curs],[ValoareLei],[DataDocJust],[TipDocJust],[NumarDocJust],[ExplicatiiDocJust],[NrCrt],[CodUnitate]) VALUES ('" +
                        numeD + "','" + numeC + "',0," + IDDocument.ToString() + "," +
                        Convert.ToDouble(dbRul["Val_valuta"]).ToString("F2", CultureInfo.InvariantCulture) + ",0," +
                        Convert.ToDouble(dbRul["Val_finala"]).ToString("F2", CultureInfo.InvariantCulture) + ",'" +
                        datai.Year.ToString() + "-" + datai.Month.ToString() + "-" + datai.Day.ToString() + "','" +
                        dbRul["tip_doc"].ToString().Trim() + "'," +
                        dbRul["nr_doc"].ToString().Trim() + ",'" +
                        dbRul["expl"].ToString().Trim() + "'," + NrCrt.ToString() + "," + ModuleInfo.CodUnitate.ToString() + ")";

                    cmd.ExecuteNonQuery();
                    pBar.Value++; // CultureInfo.InvariantCul

                }
                /// modifica ultimul document
                cmd.CommandText = "UPDATE [RulajeDoc]  SET [ValoareValuta] = " +
                      Convert.ToDouble(SumaValuta).ToString("F2", CultureInfo.InvariantCulture) + ", [ValoareLei]=" +
                      Convert.ToDouble(Suma).ToString("F2", CultureInfo.InvariantCulture) + " WHERE [IDDocument] = " + IDDocument.ToString();
                cmd.ExecuteNonQuery();

                tran.Commit();
            }
            catch (Exception ex)
            {

                MessageBox.Show(ex.Message.ToString() + ",\n" + cmd.CommandText);
                tran.Rollback();
                return false;
            }
            finally
            {
                if (dbRul != null)
                    dbRul.Close();
                dBaseConnection.Close();
            }


            dbRul.Close();
            dBaseConnection.Close();
            return true;

        }

        override public void SetCurentMonth(short vCodUnitate)
        {
            //Setup(conn);
        }

        private void Import_SQL(object sender, EventArgs e)
        {
            if (ServerTB.Text == string.Empty || BazaDateTB.Text == String.Empty)
            {
                MessageBox.Show("Completati serverul si baza de date!",
                        "Stop",
                        MessageBoxButtons.OK,
                        MessageBoxIcon.Stop);
                return;
            }
            SqlCommand cmd = new SqlCommand("amix_ImportVersiuneAnterioara", DeschideConexiune(workConnString));
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandTimeout = 180;
            cmd.Parameters.Add(new SqlParameter("ServerName",ServerTB.Text));
            cmd.Parameters.Add(new SqlParameter("DBName", BazaDateTB.Text));
            cmd.Parameters.Add(new SqlParameter("CodUnitate", ModuleInfo.CodUnitate.ToString()));
            cmd.ExecuteNonQuery();
            InchideConexiune(cmd.Connection);
            MessageBox.Show("Datele au fost preluate corect!",
                    "Info",
                    MessageBoxButtons.OK,
                    MessageBoxIcon.Information);
            
        }
        /// <summary>
        /// cand se apasa enter, controlul activ este luna, anul
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        override public void toolStripLabelAnLuna_Click(object sender, EventArgs e)
        {

        }

        private Boolean ImportMagnum(String Vcale,String denUnitate, SqlConnection con, String[] tipuri)
        {
            Int32 codParinte = 0;
            Int32 idTipDepart = 0;

            pBar.Value = 0;
            String Cont_Parinte = String.Empty, den = String.Empty, an2 = String.Empty;
            System.Data.OleDb.OleDbConnection dBaseConnection = null;
            System.Data.OleDb.OleDbDataReader dbCont = null;

            SqlTransaction tran = con.BeginTransaction();
            SqlCommand cmd = new SqlCommand("", con);
            cmd.CommandTimeout = 180;
            SqlDataReader dr = null;

            #region Inserare unitate din magnum ca si departament
            cmd.Transaction = tran;
            cmd.CommandText = "SELECT * FROM [TipDepartament]";
            dr = cmd.ExecuteReader();
            if (dr != null)
            {
                if (dr.Read())
                {
                    idTipDepart = Convert.ToInt32(dr["IDTipDepartament"]);
                }
                dr.Close();
            }

            cmd.CommandText = "SELECT * FROM [Departamente] WHERE [CodUnitate]=" + ModuleInfo.CodUnitate.ToString();
            dr = cmd.ExecuteReader();
            if (dr != null)
            {
                if (dr.Read())
                {
                    codParinte = Convert.ToInt32(dr["IDDepartParinte"]);
                }
                dr.Close();
            }

            if (StergeCB.Checked)
            {

                cmd.CommandText = "DELETE FROM [AmixRegistru] WHERE  [CodUnitate]=" + ModuleInfo.CodUnitate.ToString() + " AND [InFolosinta] = " + (InFolosintaCB.Checked ? "1" : "0") + " AND Compus>=3 ";
                cmd.ExecuteScalar();
                cmd.CommandText = "DELETE FROM [Departamente] WHERE [CodUnitate]=" + ModuleInfo.CodUnitate.ToString() + " AND [Cod] = 'OBJ" + (InFolosintaCB.Checked ? "1" : "0") + "'";
                cmd.ExecuteScalar();
            }

            cmd.CommandText = "INSERT INTO [Departamente] ([Activ],[Cod],[CodUnitate],[Nume],[IDDepartParinte],[IDTipDepartament]) VALUES (1,'OBJ" + (InFolosintaCB.Checked ? "1" : "0") + "'," +
                this.ModuleInfo.CodUnitate.ToString() + ",'" +
                denUnitate + "'," + codParinte.ToString() + "," + idTipDepart.ToString() + ");SELECT [IDDepartament] FROM Departamente WHERE IDDepartament = SCOPE_IDENTITY()";
            Object idNou = cmd.ExecuteScalar(); 

            #endregion 

            String cale = Vcale.Replace("ARHIVA", String.Empty);
            String ConnectionString = "Provider=VFPOLEDB.1;Data Source = " + cale + ";";
            dBaseConnection = new System.Data.OleDb.OleDbConnection(ConnectionString);
            dBaseConnection.Open();


            OleDbCommand dBaseCommand = new System.Data.OleDb.OleDbCommand("", dBaseConnection);
            dBaseCommand.CommandText = "SELECT * FROM [MAGM]";
            try
            {

                dbCont = dBaseCommand.ExecuteReader();
                if (dbCont == null)
                {
                    return true;
                }
                pBar.Maximum = dbCont.RecordsAffected;
                while (dbCont.Read())
                {
                    String  codn = dbCont["cod"].ToString().Trim();
                    den = dbCont["expl"].ToString().Trim();
                    Decimal stoc_fin = Convert.ToDecimal(dbCont["stoc_fin"]);
                    Int32 stoc = Convert.ToInt32(stoc_fin);
                    if (stoc == 0)
                        continue;
                    DateTime datain = Convert.ToDateTime(dbCont["Dum"]);
                    String cont = dbCont["Cont_sin"].ToString().Trim();
                    String cnt1 = dbCont["Cont_an1"].ToString().Trim();
                    if (cnt1 != string.Empty)
                        cont += "." + cnt1;
                    String cnt2 = dbCont["Cont_an2"].ToString().Trim();
                    if (cnt2 != string.Empty && Convert.ToInt32(cnt2)!=0 )
                        cont += "." + cnt2;
                    Decimal val_inv = Convert.ToDecimal(dbCont["pret_init"]);
                    Decimal stoc_fap = Convert.ToDecimal(dbCont["faptic"]);
                    Int32 stocf = Convert.ToInt32(stoc_fap);
                    Int16 idx = Convert.ToInt16(dbCont["tip_obiect"]);
                    String categorie = "Nedefinit";
                    if (idx <= 15)
                        categorie = tipuri[idx - 1];

                    cmd.CommandText = "INSERT INTO [AmixRegistru] ([NRInventar],[CantitateOI],[DataIntrare],[ContCheltuiala],[Denumire],[ValoareInventar],[StocFaptic],[IDGestionar],[CodUnitate],[InFolosinta],[Compus],[IDDepartament],[IDStare],[Categorie]) VALUES ('" +
                        codn + "'," + stoc.ToString() + ",'" +datain.Year.ToString() + "-" + datain.Month.ToString() + "-" + datain.Day.ToString()  + "','" + cont +"','"+den + "'," + val_inv.ToString("G", CultureInfo.InvariantCulture) + "," + stocf.ToString() + ",1,"  +
                        this.ModuleInfo.CodUnitate.ToString() + "," +(InFolosintaCB.Checked?"1":"0")  + "," + (stoc==1?"3":"4")+ "," + idNou.ToString() + ",5,'"+categorie + "'" +  ")";
                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    catch (SqlException exs)
                    {
                        if (exs.Number == 2627)
                        {
                            //schimbanumarul de inventar
                            codn += DateTime.Now.Millisecond.ToString();
                            cmd.CommandText = "INSERT INTO [AmixRegistru] ([NRInventar],[CantitateOI],[DataIntrare],[ContCheltuiala],[Denumire],[ValoareInventar],[StocFaptic],[IDGestionar],[CodUnitate],[InFolosinta],[Compus],[IDDepartament],[IDStare],[Categorie]) VALUES ('" +
                                codn + "'," + stoc.ToString() + ",'" + datain.Year.ToString() + "-" + datain.Month.ToString() + "-" + datain.Day.ToString() + "','" + cont + "','" + den + "'," + val_inv.ToString("G", CultureInfo.InvariantCulture) + "," + stocf.ToString() + ",1," +
                                this.ModuleInfo.CodUnitate.ToString() + "," + (InFolosintaCB.Checked ? "1" : "0") + "," + (stoc == 1 ? "3" : "4") + "," + idNou.ToString() + ",5,'" + categorie + "'" + ")";
                            cmd.ExecuteNonQuery();
                            continue;
                        }
                        throw exs;
                    }

                    pBar.Value++; // CultureInfo.InvariantCulture
                }

                tran.Commit();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message.ToString() + ",\n" + cmd.CommandText);
                tran.Rollback();
                return false;
            }
            finally
            {
                if (dbCont != null)
                    dbCont.Close();
                dBaseConnection.Close();
            }


            dbCont.Close();
            dBaseConnection.Close();
            return true;

        }

        private void ImportaDepartamente_Click(object sender, EventArgs e)
        {
            if (ServerTB.Text == string.Empty || BazaDateTB.Text == String.Empty)
            {
                MessageBox.Show("Completati serverul si baza de date!",
                        "Stop",
                        MessageBoxButtons.OK,
                        MessageBoxIcon.Stop);
                return;
            }

            String newLocalCS = GetConnectionString(workConnString);
            SqlConnectionStringBuilder conSB = new SqlConnectionStringBuilder(newLocalCS);
            conSB.DataSource = ServerTB.Text;
            conSB.InitialCatalog = BazaDateTB.Text;
            SqlConnection con = DeschideConexiuneFaraConfigurare(conSB.ConnectionString);
            this.a_act_SECTIITableAdapter.Connection = con;
            this.a_act_SECTIITableAdapter.Fill(this.eRPDataSet.a_act_SECTII);

            SqlCommand cmd = new SqlCommand("INSERT INTO [Departamente](Nume, Cod, IDDepartParinte, IDTipDepartament, CodUnitate) VALUES (", DeschideConexiune(workConnString));
            //cmd.CommandType = CommandType.StoredProcedure;
            //cmd.CommandTimeout = 180;
            //cmd.Parameters.Add(new SqlParameter("ServerName", ServerTB.Text));
            //cmd.Parameters.Add(new SqlParameter("DBName", BazaDateTB.Text));
            //cmd.Parameters.Add(new SqlParameter("CodUnitate", ModuleInfo.CodUnitate.ToString()));

            foreach (DataRowView rd in a_act_SECTIIBindingSource)
            {
                cmd.CommandText = "INSERT INTO [Departamente](Nume, Cod, IDDepartParinte, IDTipDepartament, CodUnitate,Activ) VALUES (";
                ERPDataSet.a_act_SECTIIRow rdw = rd.Row as ERPDataSet.a_act_SECTIIRow;
                String NumeDepartament = String.Empty;
                MatchCollection matches = Regex.Matches(rdw.DEN, @"(?<cod>[0-9]*)(?<rest>[\w\s\W\S]+)");
                if (matches[0].Success)
                {
                    NumeDepartament = matches[0].Groups["rest"].Value.ToString();
                }
                cmd.CommandText += "'" + NumeDepartament + "','" + rdw.SEC.ToString() + "',0,14," + ModuleInfo.CodUnitate.ToString() + ",1)";
                cmd.ExecuteNonQuery();                
            }


            
            InchideConexiune(con);
            MessageBox.Show("Datele au fost preluate corect!",
                    "Info",
                    MessageBoxButtons.OK,
                    MessageBoxIcon.Information);
        }

        

    }
}
